﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace Customize.Excel.Tests
{
    public class NPOIExcel
    {
        #region 1.使用NPOI创建EXCEL
        public void Test1()
        {
            //说明：HSSFWorkbook 用于创建  .xls  
            //      XSSFWorkbook 用于创建  .xlsx  
            //1.创建EXCEL中的Workbook  

            IWorkbook myHSSFworkbook = new HSSFWorkbook();
            IWorkbook myXSSFworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet  
            ISheet mysheetHSSF = myHSSFworkbook.CreateSheet("sheet1");
            ISheet mysheetXSSF = myXSSFworkbook.CreateSheet("sheet1");

            //3.创建Sheet中的Row  
            IRow rowHSSF = mysheetHSSF.CreateRow(0);
            //SetCellValue有5个重载方法 bool、DateTime、double、string、IRichTextString(未演示)  
            rowHSSF.CreateCell(0).SetCellValue(true);
            rowHSSF.CreateCell(1).SetCellValue(DateTime.Now);
            rowHSSF.CreateCell(2).SetCellValue(9.32);
            rowHSSF.CreateCell(3).SetCellValue("Hello World！");
            //4.创建Row中的Cell并赋值  
            IRow rowXSSF = mysheetXSSF.CreateRow(0);
            rowXSSF.CreateCell(0).SetCellValue(false);
            rowXSSF.CreateCell(1).SetCellValue(DateTime.Now);
            rowXSSF.CreateCell(2).SetCellValue(9.32);
            rowXSSF.CreateCell(3).SetCellValue("Hello World！");


            //5.保存   
            SaveExcel(myHSSFworkbook, "NPOI-1.xls");
            SaveExcel(myXSSFworkbook, "NPOI-1.xlsx");

        }
        #endregion

        #region 2.设置EXCEL列宽、行高与合并单元格
        public void Test2()
        {
            //说明：设置列宽、行高与合并单元格  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");

            //4.合并单元格区域 例： 第1行到第1行 第2列到第3列围成的矩形区域  
            mysheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 2));

            //5.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("0-0");
            row0.CreateCell(1).SetCellValue("0-1");
            row0.CreateCell(3).SetCellValue("0-3");

            //6.设置列宽   SetColumnWidth(列的索引号从0开始, N * 256) 第二个参数的单位是1/256个字符宽度。例：将第四列宽度设置为了30个字符。  
            mysheet.SetColumnWidth(3, 30 * 256);

            //7.设置行高   Height的单位是1/20个点。例：设置高度为50个点  
            row0.Height = 50 * 20;

            //5.保存         
            SaveExcel(myworkbook, "NPOI-2.xlsx");
        }
        #endregion

        #region 3.设置EXCEL单元格对齐方式
        public void Test3()
        {

            //说明：设置单元格对齐方式  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");

            mysheet.SetColumnWidth(0, 24 * 256);
            mysheet.SetColumnWidth(1, 24 * 256);
            mysheet.SetColumnWidth(2, 24 * 256);
            mysheet.SetColumnWidth(3, 24 * 256);

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0);
            row0.Height = 50 * 20;
            row0.CreateCell(0).SetCellValue("对齐方式");
            row0.CreateCell(1).SetCellValue("对齐方式");
            row0.CreateCell(2).SetCellValue("对齐方式");
            row0.CreateCell(3).SetCellValue("对齐方式");

            IRow row1 = mysheet.CreateRow(1);
            row1.Height = 50 * 20;
            row1.CreateCell(0).SetCellValue("对齐方式");
            row1.CreateCell(1).SetCellValue("Shanghai is the largest city by population in ");
            row1.CreateCell(2).SetCellValue("对齐方式");
            row1.CreateCell(3).SetCellValue("对齐方式");

            //4.创建CellStyle  
            ICellStyle style0 = myworkbook.CreateCellStyle();
            style0.Alignment = HorizontalAlignment.General;//【General】数字、时间默认：右对齐；BOOL：默认居中；字符串：默认左对齐  

            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Left;//【Left】左对齐  

            ICellStyle style2 = myworkbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.Center;//【Center】居中  

            ICellStyle style3 = myworkbook.CreateCellStyle();
            style3.Alignment = HorizontalAlignment.Right;//【Right】右对齐  

            ICellStyle style4 = myworkbook.CreateCellStyle();
            style4.Alignment = HorizontalAlignment.Fill;//【Fill】填充  

            ICellStyle style5 = myworkbook.CreateCellStyle();
            style5.Alignment = HorizontalAlignment.Justify;//【Justify】两端对齐[会自动换行]（主要针对英文）  

            ICellStyle style6 = myworkbook.CreateCellStyle();
            style6.Alignment = HorizontalAlignment.CenterSelection;//【CenterSelection】跨列居中  

            ICellStyle style7 = myworkbook.CreateCellStyle();
            style7.Alignment = HorizontalAlignment.Distributed;//【Distributed】分散对齐[会自动换行]  

            //【Tips】  
            // 1.通过ICellStyle的VerticalAlignment属性可以设置垂直对齐模式与水平对齐无异 不再演示  
            // 2.通过ISheet的SetDefaultColumnStyle(int column, ICellStyle style)方法可以设置整列的默认单元格样式;  

            //5.将CellStyle应用于具体单元格  
            row0.GetCell(0).CellStyle = style0;
            row0.GetCell(1).CellStyle = style1;
            row0.GetCell(2).CellStyle = style2;
            row0.GetCell(3).CellStyle = style3;

            row1.GetCell(0).CellStyle = style4;
            row1.GetCell(1).CellStyle = style5;
            row1.GetCell(2).CellStyle = style6;
            row1.GetCell(3).CellStyle = style7;

            //6.保存         
            SaveExcel(myworkbook, "NPOI-3.xlsx");
        }
        #endregion

        #region 4.设置EXCEL单元格背景与图案
        public void Test4()
        {
            //说明：设置单元格背景与图案  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");
            mysheet.DefaultRowHeight = 30 * 20;
            mysheet.SetColumnWidth(1, 30 * 256);

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue(""); row0.CreateCell(1).SetCellValue("NoFill");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue(""); row1.CreateCell(1).SetCellValue("SolidForeground");
            IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue(""); row2.CreateCell(1).SetCellValue("FineDots");
            IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue(""); row3.CreateCell(1).SetCellValue("AltBars");
            IRow row4 = mysheet.CreateRow(4); row4.CreateCell(0).SetCellValue(""); row4.CreateCell(1).SetCellValue("SparseDots");
            IRow row5 = mysheet.CreateRow(5); row5.CreateCell(0).SetCellValue(""); row5.CreateCell(1).SetCellValue("ThickHorizontalBands");
            IRow row6 = mysheet.CreateRow(6); row6.CreateCell(0).SetCellValue(""); row6.CreateCell(1).SetCellValue("ThickVerticalBands");
            IRow row7 = mysheet.CreateRow(7); row7.CreateCell(0).SetCellValue(""); row7.CreateCell(1).SetCellValue("ThickBackwardDiagonals");
            IRow row8 = mysheet.CreateRow(8); row8.CreateCell(0).SetCellValue(""); row8.CreateCell(1).SetCellValue("ThickForwardDiagonals");
            IRow row9 = mysheet.CreateRow(9); row9.CreateCell(0).SetCellValue(""); row9.CreateCell(1).SetCellValue("BigSpots");
            IRow row10 = mysheet.CreateRow(10); row10.CreateCell(0).SetCellValue(""); row10.CreateCell(1).SetCellValue("Bricks");
            IRow row11 = mysheet.CreateRow(11); row11.CreateCell(0).SetCellValue(""); row11.CreateCell(1).SetCellValue("ThinHorizontalBands");
            IRow row12 = mysheet.CreateRow(12); row12.CreateCell(0).SetCellValue(""); row12.CreateCell(1).SetCellValue("ThinVerticalBands");
            IRow row13 = mysheet.CreateRow(13); row13.CreateCell(0).SetCellValue(""); row13.CreateCell(1).SetCellValue("ThinBackwardDiagonals");
            IRow row14 = mysheet.CreateRow(14); row14.CreateCell(0).SetCellValue(""); row14.CreateCell(1).SetCellValue("ThinForwardDiagonals");
            IRow row15 = mysheet.CreateRow(15); row15.CreateCell(0).SetCellValue(""); row15.CreateCell(1).SetCellValue("Squares");
            IRow row16 = mysheet.CreateRow(16); row16.CreateCell(0).SetCellValue(""); row16.CreateCell(1).SetCellValue("Diamonds");
            IRow row17 = mysheet.CreateRow(17); row17.CreateCell(0).SetCellValue(""); row17.CreateCell(1).SetCellValue("LessDots");
            IRow row18 = mysheet.CreateRow(18); row18.CreateCell(0).SetCellValue(""); row18.CreateCell(1).SetCellValue("LeastDots");

            //【Tips】  
            // 1.ForegroundColor(默认黑色)【前景颜色】BackgroundColor(默认为前景颜色的反色)【背景颜色】Pattern(必须指定，默认NoFill)【填充的图案】  
            // 2.演示中使用 【前景颜色】黑色 【背景颜色】白色  

            //4.创建CellStyle并应用于单元格    
            ICellStyle style0 = myworkbook.CreateCellStyle(); style0.FillBackgroundColor = IndexedColors.White.Index;
            style0.FillForegroundColor = IndexedColors.Black.Index; style0.FillPattern = FillPattern.NoFill;
            row0.GetCell(0).CellStyle = style0;
            ICellStyle style1 = myworkbook.CreateCellStyle(); style1.FillBackgroundColor = IndexedColors.White.Index;
            style1.FillForegroundColor = IndexedColors.Black.Index; style1.FillPattern = FillPattern.SolidForeground;
            row1.GetCell(0).CellStyle = style1;
            ICellStyle style2 = myworkbook.CreateCellStyle(); style2.FillBackgroundColor = IndexedColors.White.Index;
            style2.FillForegroundColor = IndexedColors.Black.Index; style2.FillPattern = FillPattern.FineDots;
            row2.GetCell(0).CellStyle = style2;
            ICellStyle style3 = myworkbook.CreateCellStyle(); style3.FillBackgroundColor = IndexedColors.White.Index;
            style3.FillForegroundColor = IndexedColors.Black.Index; style3.FillPattern = FillPattern.AltBars;
            row3.GetCell(0).CellStyle = style3;
            ICellStyle style4 = myworkbook.CreateCellStyle(); style4.FillBackgroundColor = IndexedColors.White.Index;
            style4.FillForegroundColor = IndexedColors.Black.Index; style4.FillPattern = FillPattern.SparseDots;
            row4.GetCell(0).CellStyle = style4;
            ICellStyle style5 = myworkbook.CreateCellStyle(); style5.FillBackgroundColor = IndexedColors.White.Index;
            style5.FillForegroundColor = IndexedColors.Black.Index; style5.FillPattern = FillPattern.ThickHorizontalBands;
            row5.GetCell(0).CellStyle = style5;
            ICellStyle style6 = myworkbook.CreateCellStyle(); style6.FillBackgroundColor = IndexedColors.White.Index;
            style6.FillForegroundColor = IndexedColors.Black.Index; style6.FillPattern = FillPattern.ThickVerticalBands;
            row6.GetCell(0).CellStyle = style6;
            ICellStyle style7 = myworkbook.CreateCellStyle(); style7.FillBackgroundColor = IndexedColors.White.Index;
            style7.FillForegroundColor = IndexedColors.Black.Index; style7.FillPattern = FillPattern.ThickBackwardDiagonals;
            row7.GetCell(0).CellStyle = style7;
            ICellStyle style8 = myworkbook.CreateCellStyle(); style8.FillBackgroundColor = IndexedColors.White.Index;
            style8.FillForegroundColor = IndexedColors.Black.Index; style8.FillPattern = FillPattern.ThickForwardDiagonals;
            row8.GetCell(0).CellStyle = style8;
            ICellStyle style9 = myworkbook.CreateCellStyle(); style9.FillBackgroundColor = IndexedColors.White.Index;
            style9.FillForegroundColor = IndexedColors.Black.Index; style9.FillPattern = FillPattern.BigSpots;
            row9.GetCell(0).CellStyle = style9;
            ICellStyle style10 = myworkbook.CreateCellStyle(); style10.FillBackgroundColor = IndexedColors.White.Index;
            style10.FillForegroundColor = IndexedColors.Black.Index; style10.FillPattern = FillPattern.Bricks;
            row10.GetCell(0).CellStyle = style10;
            ICellStyle style11 = myworkbook.CreateCellStyle(); style11.FillBackgroundColor = IndexedColors.White.Index;
            style11.FillForegroundColor = IndexedColors.Black.Index; style11.FillPattern = FillPattern.ThinHorizontalBands;
            row11.GetCell(0).CellStyle = style11;
            ICellStyle style12 = myworkbook.CreateCellStyle(); style12.FillBackgroundColor = IndexedColors.White.Index;
            style12.FillForegroundColor = IndexedColors.Black.Index; style12.FillPattern = FillPattern.ThinVerticalBands;
            row12.GetCell(0).CellStyle = style12;
            ICellStyle style13 = myworkbook.CreateCellStyle(); style13.FillBackgroundColor = IndexedColors.White.Index;
            style13.FillForegroundColor = IndexedColors.Black.Index; style13.FillPattern = FillPattern.ThinBackwardDiagonals;
            row13.GetCell(0).CellStyle = style13;
            ICellStyle style14 = myworkbook.CreateCellStyle(); style14.FillBackgroundColor = IndexedColors.White.Index;
            style14.FillForegroundColor = IndexedColors.Black.Index; style14.FillPattern = FillPattern.ThinForwardDiagonals;
            row14.GetCell(0).CellStyle = style14;
            ICellStyle style15 = myworkbook.CreateCellStyle(); style15.FillBackgroundColor = IndexedColors.White.Index;
            style15.FillForegroundColor = IndexedColors.Black.Index; style15.FillPattern = FillPattern.Squares;
            row15.GetCell(0).CellStyle = style15;
            ICellStyle style16 = myworkbook.CreateCellStyle(); style16.FillBackgroundColor = IndexedColors.White.Index;
            style16.FillForegroundColor = IndexedColors.Black.Index; style16.FillPattern = FillPattern.Diamonds;
            row16.GetCell(0).CellStyle = style16;
            ICellStyle style17 = myworkbook.CreateCellStyle(); style17.FillBackgroundColor = IndexedColors.White.Index;
            style17.FillForegroundColor = IndexedColors.Black.Index; style17.FillPattern = FillPattern.LessDots;
            row17.GetCell(0).CellStyle = style17;
            ICellStyle style18 = myworkbook.CreateCellStyle(); style18.FillBackgroundColor = IndexedColors.White.Index;
            style18.FillForegroundColor = IndexedColors.Black.Index; style18.FillPattern = FillPattern.LeastDots;
            row18.GetCell(0).CellStyle = style18;

            //5.保存         
            SaveExcel(myworkbook, "NPOI-4.xlsx");
        }
        #endregion

        #region 5.设置EXCEL单元格边框
        public void Test5()
        {
            //说明：设置单元格边框  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue(""); row0.CreateCell(1).SetCellValue("Thin");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue(""); row1.CreateCell(1).SetCellValue("Medium");
            IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue(""); row2.CreateCell(1).SetCellValue("Dashed");
            IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue(""); row3.CreateCell(1).SetCellValue("Dotted");
            IRow row4 = mysheet.CreateRow(4); row4.CreateCell(0).SetCellValue(""); row4.CreateCell(1).SetCellValue("Thick");
            IRow row5 = mysheet.CreateRow(5); row5.CreateCell(0).SetCellValue(""); row5.CreateCell(1).SetCellValue("Double");
            IRow row6 = mysheet.CreateRow(6); row6.CreateCell(0).SetCellValue(""); row6.CreateCell(1).SetCellValue("Hair");
            IRow row7 = mysheet.CreateRow(7); row7.CreateCell(0).SetCellValue(""); row7.CreateCell(1).SetCellValue("MediumDashed");
            IRow row8 = mysheet.CreateRow(8); row8.CreateCell(0).SetCellValue(""); row8.CreateCell(1).SetCellValue("DashDot");
            IRow row9 = mysheet.CreateRow(9); row9.CreateCell(0).SetCellValue(""); row9.CreateCell(1).SetCellValue("MediumDashDot");
            IRow row10 = mysheet.CreateRow(10); row10.CreateCell(0).SetCellValue(""); row10.CreateCell(1).SetCellValue("DashDotDot");
            IRow row11 = mysheet.CreateRow(11); row11.CreateCell(0).SetCellValue(""); row11.CreateCell(1).SetCellValue("MediumDashDotDot");
            IRow row12 = mysheet.CreateRow(12); row12.CreateCell(0).SetCellValue(""); row12.CreateCell(1).SetCellValue("SlantedDashDot");
            IRow row13 = mysheet.CreateRow(13); row13.CreateCell(0).SetCellValue(""); row13.CreateCell(1).SetCellValue("BorderDiagonal.Backward");
            IRow row14 = mysheet.CreateRow(14); row14.CreateCell(0).SetCellValue(""); row14.CreateCell(1).SetCellValue("BorderDiagonal.Forward");
            IRow row15 = mysheet.CreateRow(15); row15.CreateCell(0).SetCellValue(""); row15.CreateCell(1).SetCellValue("BorderDiagonal.Both");

            //4.创建CellStyle  
            ICellStyle style0 = myworkbook.CreateCellStyle();
            style0.BorderBottom = BorderStyle.Thin;

            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.BorderBottom = BorderStyle.Medium;

            ICellStyle style2 = myworkbook.CreateCellStyle();
            style2.BorderBottom = BorderStyle.Dashed;

            ICellStyle style3 = myworkbook.CreateCellStyle();
            style3.BorderBottom = BorderStyle.Dotted;

            ICellStyle style4 = myworkbook.CreateCellStyle();
            style4.BorderBottom = BorderStyle.Thick;

            ICellStyle style5 = myworkbook.CreateCellStyle();
            style5.BorderBottom = BorderStyle.Double;

            ICellStyle style6 = myworkbook.CreateCellStyle();
            style6.BorderBottom = BorderStyle.Hair;

            ICellStyle style7 = myworkbook.CreateCellStyle();
            style7.BorderBottom = BorderStyle.MediumDashed;

            ICellStyle style8 = myworkbook.CreateCellStyle();
            style8.BorderBottom = BorderStyle.DashDot;

            ICellStyle style9 = myworkbook.CreateCellStyle();
            style9.BorderBottom = BorderStyle.MediumDashDot;

            ICellStyle style10 = myworkbook.CreateCellStyle();
            style10.BorderBottom = BorderStyle.DashDotDot;

            ICellStyle style11 = myworkbook.CreateCellStyle();
            style11.BorderBottom = BorderStyle.MediumDashDotDot;

            ICellStyle style12 = myworkbook.CreateCellStyle();
            style12.BorderBottom = BorderStyle.SlantedDashDot;

            ICellStyle style13 = myworkbook.CreateCellStyle();
            style13.BorderDiagonalLineStyle = BorderStyle.Thin;
            style13.BorderDiagonal = BorderDiagonal.Backward;
            style13.BorderDiagonalColor = IndexedColors.Red.Index;

            ICellStyle style14 = myworkbook.CreateCellStyle();
            style14.BorderDiagonalLineStyle = BorderStyle.Thin;
            style14.BorderDiagonal = BorderDiagonal.Forward;
            style14.BorderDiagonalColor = IndexedColors.Red.Index;

            ICellStyle style15 = myworkbook.CreateCellStyle();
            style15.BorderDiagonalLineStyle = BorderStyle.Thin;
            style15.BorderDiagonal = BorderDiagonal.Both;
            style15.BorderDiagonalColor = IndexedColors.Red.Index;

            //【Tips】  
            // 1.Border+方向 [边框类型] 例：BorderTop, BorderBottom,BorderLeft, BorderRight  
            // 2.方向+BorderColor [边框颜色] 例：TopBorderColor,BottomBorderColor, LeftBorderColor, RightBorderColor  
            // 3.绘制斜线首先要指定 BorderDiagonalLineStyle 然后 指定 BorderDiagonal   

            //5.将CellStyle应用于具体单元格  
            row0.GetCell(0).CellStyle = style0;
            row1.GetCell(0).CellStyle = style1;
            row2.GetCell(0).CellStyle = style2;
            row3.GetCell(0).CellStyle = style3;
            row4.GetCell(0).CellStyle = style4;
            row5.GetCell(0).CellStyle = style5;
            row6.GetCell(0).CellStyle = style6;
            row7.GetCell(0).CellStyle = style7;
            row8.GetCell(0).CellStyle = style8;
            row9.GetCell(0).CellStyle = style9;
            row10.GetCell(0).CellStyle = style10;
            row11.GetCell(0).CellStyle = style11;
            row12.GetCell(0).CellStyle = style12;
            row13.GetCell(0).CellStyle = style13;
            row14.GetCell(0).CellStyle = style14;
            row15.GetCell(0).CellStyle = style15;

            //6.保存         
            SaveExcel(myworkbook, "NPOI-5.xlsx");
        }
        #endregion

        #region 6.设置字体
        public void Test6()
        {
            //说明：设置字体  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");
            mysheet.DefaultRowHeight = 20 * 20;

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue("中文"); row0.CreateCell(1).SetCellValue("原始字体");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue("中文"); row1.CreateCell(1).SetCellValue("Boldweight");
            IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue("中文"); row2.CreateCell(1).SetCellValue("Color");
            IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue("中文"); row3.CreateCell(1).SetCellValue("FontHeight");
            IRow row4 = mysheet.CreateRow(4); row4.CreateCell(0).SetCellValue("中文"); row4.CreateCell(1).SetCellValue("FontHeightInPoints");
            IRow row5 = mysheet.CreateRow(5); row5.CreateCell(0).SetCellValue("中文"); row5.CreateCell(1).SetCellValue("FontName");
            IRow row6 = mysheet.CreateRow(6); row6.CreateCell(0).SetCellValue("中文"); row6.CreateCell(1).SetCellValue("IsBold");
            IRow row7 = mysheet.CreateRow(7); row7.CreateCell(0).SetCellValue("中文"); row7.CreateCell(1).SetCellValue("IsItalic");
            IRow row8 = mysheet.CreateRow(8); row8.CreateCell(0).SetCellValue("中文"); row8.CreateCell(1).SetCellValue("IsStrikeout");
            IRow row9 = mysheet.CreateRow(9); row9.CreateCell(0).SetCellValue("中文"); row9.CreateCell(1).SetCellValue("TypeOffset");
            IRow row10 = mysheet.CreateRow(10); row10.CreateCell(0).SetCellValue("中文"); row10.CreateCell(1).SetCellValue("Underline");

            //4.设置字体样式  
            IFont font1 = myworkbook.CreateFont();
            font1.Boldweight = (Int16)FontBoldWeight.Bold;

            //【Tips】  
            // 1.Boldweight 要使用(Int16)FontBoldWeight 对应的数值 否则无效  

            IFont font2 = myworkbook.CreateFont();
            font2.Color = IndexedColors.Red.Index;

            IFont font3 = myworkbook.CreateFont();
            font3.FontHeight = 17;

            IFont font4 = myworkbook.CreateFont();
            font4.FontHeightInPoints = 17;

            IFont font5 = myworkbook.CreateFont();
            font5.FontName = "黑体";

            IFont font6 = myworkbook.CreateFont();
            font6.IsBold = true;

            IFont font7 = myworkbook.CreateFont();
            font7.IsItalic = true;

            IFont font8 = myworkbook.CreateFont();
            font8.IsStrikeout = true;

            IFont font9 = myworkbook.CreateFont();
            font9.TypeOffset = FontSuperScript.Sub;

            IFont font10 = myworkbook.CreateFont();
            font10.Underline = FontUnderlineType.Single;

            //5.创建CellStyle并加载字体  
            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.SetFont(font1);

            ICellStyle style2 = myworkbook.CreateCellStyle();
            style2.SetFont(font2);

            ICellStyle style3 = myworkbook.CreateCellStyle();
            style3.SetFont(font3);

            ICellStyle style4 = myworkbook.CreateCellStyle();
            style4.SetFont(font4);

            ICellStyle style5 = myworkbook.CreateCellStyle();
            style5.SetFont(font5);

            ICellStyle style6 = myworkbook.CreateCellStyle();
            style6.SetFont(font6);

            ICellStyle style7 = myworkbook.CreateCellStyle();
            style7.SetFont(font7);

            ICellStyle style8 = myworkbook.CreateCellStyle();
            style8.SetFont(font8);

            ICellStyle style9 = myworkbook.CreateCellStyle();
            style9.SetFont(font9);

            ICellStyle style10 = myworkbook.CreateCellStyle();
            style10.SetFont(font10);

            //6.将CellStyle应用于具体单元格  
            row1.GetCell(0).CellStyle = style1;
            row2.GetCell(0).CellStyle = style2;
            row3.GetCell(0).CellStyle = style3;
            row4.GetCell(0).CellStyle = style4;
            row5.GetCell(0).CellStyle = style5;
            row6.GetCell(0).CellStyle = style6;
            row7.GetCell(0).CellStyle = style7;
            row8.GetCell(0).CellStyle = style8;
            row9.GetCell(0).CellStyle = style9;
            row10.GetCell(0).CellStyle = style10;

            //7.保存         
            SaveExcel(myworkbook, "NPOI-6.xlsx");
        }
        #endregion

        #region 7.设置数字格式
        public void Test7()
        {

            //说明：设置数字格式  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");
            mysheet.SetColumnWidth(0, 20 * 256);
            mysheet.SetColumnWidth(1, 20 * 256);

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue(2013.143); row0.CreateCell(1).SetCellValue("转化为汉字大写");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue(123152013.143); row1.CreateCell(1).SetCellValue("改变小数精度");
            IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue(123152013.143); row2.CreateCell(1).SetCellValue("分段添加，号");
            IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue(123152013.143); row3.CreateCell(1).SetCellValue("科学计数法");
            IRow row4 = mysheet.CreateRow(4); row4.CreateCell(0).SetCellValue(-123152013.143); row4.CreateCell(1).SetCellValue("正数与负数的区分(负数红色)");
            IRow row5 = mysheet.CreateRow(5); row5.CreateCell(0).SetCellValue(123152013.77); row5.CreateCell(1).SetCellValue("整数部分+分数");
            IRow row6 = mysheet.CreateRow(6); row6.CreateCell(0).SetCellValue(123152013.77); row6.CreateCell(1).SetCellValue("分数");
            IRow row7 = mysheet.CreateRow(7); row7.CreateCell(0).SetCellValue(0.333); row7.CreateCell(1).SetCellValue("百分数");

            //4.创建CellStyle与DataFormat并加载格式样式  
            IDataFormat dataformat = myworkbook.CreateDataFormat();

            ICellStyle style0 = myworkbook.CreateCellStyle();
            style0.DataFormat = dataformat.GetFormat("[DbNum2][$-804]General");//转化为汉字大写  

            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.DataFormat = dataformat.GetFormat("0.0"); //改变小数精度【小数点后有几个0表示精确到小数点后几位】  

            ICellStyle style2 = myworkbook.CreateCellStyle();
            style2.DataFormat = dataformat.GetFormat("#,##0.0");//分段添加，号  

            ICellStyle style3 = myworkbook.CreateCellStyle();
            style3.DataFormat = dataformat.GetFormat("0.00E+00");//科学计数法  

            ICellStyle style4 = myworkbook.CreateCellStyle();
            style4.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00");//正数与负数的区分  

            ICellStyle style5 = myworkbook.CreateCellStyle();
            style5.DataFormat = dataformat.GetFormat("# ??/??");//整数部分+分数  

            ICellStyle style6 = myworkbook.CreateCellStyle();
            style6.DataFormat = dataformat.GetFormat("??/??");//分数  

            ICellStyle style7 = myworkbook.CreateCellStyle();
            style7.DataFormat = dataformat.GetFormat("0.00%");//百分数【小数点后有几个0表示精确到显示小数点后几位】  

            //5.将CellStyle应用于具体单元格  
            row0.GetCell(0).CellStyle = style0;
            row1.GetCell(0).CellStyle = style1;
            row2.GetCell(0).CellStyle = style2;
            row3.GetCell(0).CellStyle = style3;
            row4.GetCell(0).CellStyle = style4;
            row5.GetCell(0).CellStyle = style5;
            row6.GetCell(0).CellStyle = style6;
            row7.GetCell(0).CellStyle = style7;

            //6.保存         
            SaveExcel(myworkbook, "NPOI-7.xlsx");
        }
        #endregion

        #region 8.设置EXCEL单元格【时间格式】
        public void Test8()
        {

            //说明：设置时间格式  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");
            mysheet.SetColumnWidth(0, 40 * 256);

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue(DateTime.Now);
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue(DateTime.Now);
            IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue(DateTime.Now);
            IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue(DateTime.Now);

            //4.创建CellStyle与DataFormat并加载格式样式  
            IDataFormat dataformat = myworkbook.CreateDataFormat();

            //【Tips】  
            // 1.yyyy 年份；    yy 年份后两位  
            // 2.MM 月份零起始；M 月份非零起始;  mmm[英文月份简写];mmmm[英文月份全称]  
            // 3.dd   日零起始；d 日非零起始  
            // 4.hh 小时零起始；h 小时非零起始[用于12小时制][12小时制必须在时间后面添加 AM/PM 或 上午/下午]  
            // 5.HH 小时零起始；H 小时非零起始[用于24小时制]  
            // 6.mm 分钟零起始；m 分钟非零起始  
            // 7.ss 秒数零起始；s 秒数非零起始  
            // 8.dddd 星期；ddd 星期缩写【英文】  
            // 9.aaaa 星期；aaa 星期缩写【中文】  
            ICellStyle style0 = myworkbook.CreateCellStyle();
            style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 aaaa");

            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 dddd");

            ICellStyle style2 = myworkbook.CreateCellStyle();
            style2.DataFormat = dataformat.GetFormat("h:mm:ss AM/PM");

            ICellStyle style3 = myworkbook.CreateCellStyle();
            style3.DataFormat = dataformat.GetFormat("h:mm:ss 上午/下午");

            //5.将CellStyle应用于具体单元格  
            row0.GetCell(0).CellStyle = style0;
            row1.GetCell(0).CellStyle = style1;
            row2.GetCell(0).CellStyle = style2;
            row3.GetCell(0).CellStyle = style3;

            //6.保存         
            SaveExcel(myworkbook, "NPOI-8.xlsx");
        }
        #endregion

        #region 9.设置文本格式
        public void Test9()
        {
            //说明：设置文本格式  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");
            mysheet.SetColumnWidth(0, 40 * 256);

            //3.创建Row中的Cell并赋值  
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue("130925199662080044");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue("" + DateTime.Now + "");

            //4.创建CellStyle与DataFormat并加载格式样式  
            IDataFormat dataformat = myworkbook.CreateDataFormat();

            //【Tips】  
            // 1.使用@ 或 text 都可以  
            // 2.再也不用为身份证号发愁了  

            ICellStyle style0 = myworkbook.CreateCellStyle();
            style0.DataFormat = dataformat.GetFormat("@");

            ICellStyle style1 = myworkbook.CreateCellStyle();
            style1.DataFormat = dataformat.GetFormat("text");

            //5.将CellStyle应用于具体单元格  
            row0.GetCell(0).CellStyle = style0;
            row1.GetCell(0).CellStyle = style1;

            //6.保存         
            FileStream file = new FileStream(@"E:\myworkbook9.xlsx", FileMode.Create);
            myworkbook.Write(file);
            file.Close();
        }
        #endregion

        #region 10.向EXCEL插入图片
        public void Test10()
        {
            //说明：插入图片  

            //1.创建EXCEL中的Workbook           
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet          
            ISheet mysheet = myworkbook.CreateSheet("sheet1");

            //http://cn.bing.com/HPImageArchive.aspx?format=js&idx=0&n=1  获取cnbing背景图片
            //第一步：读取图片到byte数组  
            WebRequest request = WebRequest.Create("http://s.cn.bing.net/az/hprichbg/rb/CavernduPontdArc_EN-US9994344414_1920x1080.jpg");

            byte[] bytes;
            using (Stream stream = request.GetResponse().GetResponseStream())
            {
                using (MemoryStream mstream = new MemoryStream())
                {
                    int count = 0;
                    byte[] buffer = new byte[1024];
                    int readNum = 0;
                    while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
                    {
                        count = count + readNum;
                        mstream.Write(buffer, 0, 1024);
                    }
                    mstream.Position = 0;
                    using (BinaryReader br = new BinaryReader(mstream))
                    {

                        bytes = br.ReadBytes(count);
                    }
                }
            }

            //第二步：将图片添加到workbook中  指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）  
            int pictureIdx = myworkbook.AddPicture(bytes, PictureType.JPEG);

            //第三步：在sheet中创建画部  
            IDrawing patriarch = mysheet.CreateDrawingPatriarch();

            //第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）  
            IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, 0, 0, 2, 2);


            //第五步：创建图片  
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

            //6.保存         
            SaveExcel(myworkbook, "NPOI-10.xlsx");
        }
        #endregion

        private void SaveExcel(IWorkbook workBook, string excelFileName)
        {
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, excelFileName);
            //6.保存         
            using (FileStream fs = new FileStream(filePath, FileMode.Create))
            {
                workBook.Write(fs);
            }
        }


        private void SaveExcel()
        {
            String Title = "XXXXXX( sdfdsfdfdsfdsfd )统计     2019年10 月";
            //文件命名
            string strFileName = "test.xls";

            //创建工作薄
            HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
            //编辑文件信息，如文件所属公司、作者、创建日期等
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NACAO";
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "NACAO";
            si.LastAuthor = "NACAO";
            si.CreateDateTime = DateTime.Now;

            hssfWorkBook.DocumentSummaryInformation = dsi;
            hssfWorkBook.SummaryInformation = si;
            GetExcel(hssfWorkBook, Title, strFileName);
            //表格构建完毕
            SaveExcel(hssfWorkBook, strFileName);

        }
        public void GetExcel(IWorkbook hssfWorkBook, string Title, string strFileName)
        {
            //创建名为YYYYYY的表sheet
            ISheet hssfSheet = hssfWorkBook.CreateSheet("YYYYYY");

            //设置列宽
            for (int c = 0; c < 7; c++)
            {
                hssfSheet.SetColumnWidth(c, 12 * 266);
            }

            //设置列头的单元格样式
            ICellStyle cellStyle = hssfWorkBook.CreateCellStyle();
            IFont cellFont = hssfWorkBook.CreateFont();
            cellFont.Boldweight = Convert.ToInt16(FontBoldWeight.Bold);
            cellFont.FontName = "宋体";
            cellFont.FontHeightInPoints = 12;
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.WrapText = true;
            cellStyle.SetFont(cellFont);
            cellStyle.BorderTop = cellStyle.BorderRight = cellStyle.BorderBottom = cellStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None
                                                                                                                           //左对齐样式
            ICellStyle leftCellStyle = hssfWorkBook.CreateCellStyle();
            leftCellStyle.CloneStyleFrom(cellStyle);
            leftCellStyle.Alignment = HorizontalAlignment.Left;
            //居中填充样式
            ICellStyle fillCellStyle = hssfWorkBook.CreateCellStyle();
            fillCellStyle.CloneStyleFrom(cellStyle);
            fillCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            //fillCellStyle.FillPattern = FillPattern.Diamonds;
            //fillCellStyle.FillPattern = FillPattern.FineDots;
            fillCellStyle.FillPattern = FillPattern.LeastDots;
            fillCellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            //居中填充样式(无边框)
            ICellStyle fillCellStyle2 = hssfWorkBook.CreateCellStyle();
            fillCellStyle2.CloneStyleFrom(fillCellStyle);
            //fillCellStyle2.FillPattern = FillPattern.NoFill;
            fillCellStyle2.BorderTop = fillCellStyle2.BorderRight = fillCellStyle2.BorderBottom = fillCellStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None
                                                                                                                                               //值的样式
            ICellStyle valueStyle = hssfWorkBook.CreateCellStyle();
            IFont valueFont = hssfWorkBook.CreateFont();
            valueFont.FontName = "宋体";
            valueFont.FontHeightInPoints = 10;
            valueStyle.BorderTop = valueStyle.BorderRight = valueStyle.BorderBottom = valueStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None
            valueStyle.WrapText = true;
            valueStyle.Alignment = HorizontalAlignment.Center;
            valueStyle.VerticalAlignment = VerticalAlignment.Center;
            valueStyle.SetFont(valueFont);
            //值的样式（无边框）
            ICellStyle valueStyle2 = hssfWorkBook.CreateCellStyle();
            valueStyle2.CloneStyleFrom(valueStyle);
            valueStyle2.BorderTop = valueStyle2.BorderRight = valueStyle2.BorderBottom = valueStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None

            //开始构建表格
            int rowIndex = 0;//记录用到第几行
                             //构建表题

            //创建表格的第一行的第一个单元格
            hssfSheet.CreateRow(0).CreateCell(0).CellStyle = fillCellStyle;
            //获取表格的第一行的第一个单元格，并为其赋值
            hssfSheet.GetRow(0).GetCell(0).SetCellValue(Title);
            //合并单元格
            /*
            * cellRangeAddress可以合并行或列，第一个参数是起始行号，第二个参数是终止行号，第三个参数是起始列号，第三个参数是终止列号
            */
            CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex + 2, 0, 6);
            hssfSheet.AddMergedRegion(region);
            //  hssfSheet.SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);

            rowIndex = rowIndex + 3; //3
            String Type = "部门";
            //构建表头
            hssfSheet.CreateRow(rowIndex).CreateCell(0).CellStyle = valueStyle;
            hssfSheet.GetRow(rowIndex).GetCell(0).SetCellValue("序号");
             //构建表格的具体细节省略，无非是合并单元格，填充数据
       

        }
    }
}
